/*
 * LppDbSetup.java
 *
 * Created on December 17, 2006, 4:23 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package com.sun.demo.addressbook.db;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Properties;

/**
 *
 * @author rhisld
 */
public class LppDbSetup {
    private static LppDbSetup ref;
    private Properties dbProperties;
    private String dbName;
    
    /** Creates a new instance of LppDbSetup */
    private LppDbSetup() {
        this.dbName = "LppDb";
        makeDBSystemDir();
        dbProperties = loadDBProperties();
        String driverName = dbProperties.getProperty("derby.driver");
        loadDatabaseDriver(driverName);
        if(!dbExists()) {
            createDatabase();
        }
    }
    
    public static LppDbSetup getLppDbSetup() {
        if (ref == null)
            ref = new LppDbSetup();
        return ref;
    }
    
    private boolean dbExists() {
        boolean bExists = false;
        String dbLocation = getDatabaseDiskLocation();
        File dbFileDir = new File(dbLocation);
        if (dbFileDir.exists()) {
            bExists = true;
            System.out.println("Database Exists at: " + dbLocation);
        } else {
            System.out.println("Database Does Not Exist at: " + dbLocation);
        }
        return bExists;
    }
    
    private void makeDBSystemDir() {
        // decide on the db system directory
        System.out.println("Checking Derby System Home");
        String systemDir = System.getProperty("derby.system.home");
        if (systemDir == null) {
            System.out.println("Derby System Home System Property not set");
            systemDir = System.getenv("derby.system.home");
        }
        if (systemDir == null) {
            System.out.println("Derby System Home Environment Variable not set");
            systemDir = System.getProperty("user.home", ".") + "\\My Documents\\Projects\\Databases";
        }
        System.out.println("user.home (System Property): " + System.getProperty("user.home", "."));
        System.out.println("Setting Derby System Home System Property: " + systemDir);
        System.setProperty("derby.system.home", systemDir);
        System.out.println("derby.system.home: " + systemDir);
        // create the db system directory
        File fileSystemDir = new File(systemDir);
        fileSystemDir.mkdir();
    }
    
    private void loadDatabaseDriver(String driverName) {
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
    }
    
    private Properties loadDBProperties() {
        InputStream dbPropInputStream = null;
        dbPropInputStream = AddressDao.class.getResourceAsStream("Configuration.properties");
        dbProperties = new Properties();
        try {
            dbProperties.load(dbPropInputStream);
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        return dbProperties;
    }
    
    private boolean createDatabase() {
        boolean bCreated = false;
        Connection dbConnection = null;
        
        String dbUrl = getDatabaseUrl();
        dbProperties.put("create", "true");
        
        try {
            dbConnection = DriverManager.getConnection(dbUrl, dbProperties);
            bCreated = createTables(dbConnection);
        } catch (SQLException ex) {
        }
        dbProperties.remove("create");
        return bCreated;
    }
    
    private boolean createTables(Connection dbConnection) {
        boolean bCreatedTables = false;
        Statement statement = null;
        try {
            statement = dbConnection.createStatement();
            statement.execute(strCreateAddressTable);
            statement = dbConnection.createStatement();
            statement.execute(strCreatePartTable);
            statement = dbConnection.createStatement();
            statement.execute(strCreateGroupTable);
            statement = dbConnection.createStatement();
            statement.execute(strCreatePartGroupTable);
            bCreatedTables = true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return bCreatedTables;
    }
    
    public Properties getDatabaseProperties() {
        return dbProperties;
    }
    
    public String getDatabaseDiskLocation() {
        String dbLocation = System.getProperty("derby.system.home") + "\\" + dbName;
        return dbLocation;
    }
    
    public String getDatabaseUrl() {
        return dbProperties.getProperty("derby.url");
    }
    
    /* New fields to add
     *
     */
    private static final String strCreateAddressTable =
            "create table APP.ADDRESS (" +
            "    ID             INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
            "    LASTNAME       VARCHAR(30), " +
            "    FIRSTNAME      VARCHAR(30), " +
            "    MIDDLENAME     VARCHAR(30), " +
            "    PHONE          VARCHAR(20), " +
            "    OFFICEPHONE    VARCHAR(20), " +
            "    CELLPHONE      VARCHAR(20), " +
            "    EMAIL          VARCHAR(100), " +
            "    ADDRESS1       VARCHAR(45), " +
            "    CITY           VARCHAR(30), " +
            "    STATE          VARCHAR(30), " +
            "    POSTALCODE     VARCHAR(20), " +
            "    AGE            SMALLINT, " +
            "    HEIGHTFEET     VARCHAR(2), " +
            "    HEIGHTINCHES   VARCHAR(2), " +
            "    OWNSCOSTUME    SMALLINT, " +
            "    COSTUMEATHOME  SMALLINT, " +
            "    COSTUMELIST    VARCHAR(250), " +
            "    PART2006       VARCHAR(90), " +
            "    SOLOAUDITION   SMALLINT, " +
            "    LOWVOCALNOTE   VARCHAR(5), " +
            "    HIGHVOCALNOTE  VARCHAR(5), " +
            "    EXPERIENCELEVEL SMALLINT, " +
            "    ASSIGNEDPART   SMALLINT, " +
            "    ACTINGSCORE    VARCHAR(5), " +
            "    VOCALSCORE     VARCHAR(5), " +
            "    NOTES          VARCHAR(32672) " +
            ")";
    
    private static final String strCreatePartTable =
            "create table APP.PART (" +
            "    ID              INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
            "    PARTNAME        VARCHAR(30), " +
            "    ISSPEAKINGPART  SMALLINT, " +
            "    ISSINGINGPART   SMALLINT, " +
            "    NOTES           VARCHAR(32672) " +
            ")";
    
    private static final String strCreateGroupTable =
            "create table APP.GROUPDATA (" +
            "    ID              INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
            "    GROUPNAME       VARCHAR(45), " +
            "    NOTES           VARCHAR(32672) " +
            ")";
    
    private static final String strCreatePartGroupTable =
            "create table APP.PARTGROUP (" +
            "    PARTID          INTEGER NOT NULL, " +
            "    GROUPID         INTEGER NOT NULL, " +
            "    CONSTRAINT PARTGROUP_PK PRIMARY KEY (PARTID, GROUPID) " +
            ")";
    
    public Object clone() throws CloneNotSupportedException {
        throw new CloneNotSupportedException();
    }
    
    
}
